package com.huawei.struts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.st.model.HtmlSelectOption;
import com.st.model.LjwUserT;

public class CommonDAO extends HibernateDaoSupport {

	/**
	 * 得到角色list
	 * @param roleId
	 * @return
	 */
	public List<HtmlSelectOption> getRoleList() {
			
			List list = getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					
					StringBuffer hql=new StringBuffer();
					String sql="select t.ROLE_ID id, t.ROLE_NAME displayName from LJW_ROLE_T t";
					 Connection con = session.connection();      
	                 PreparedStatement ps = con.prepareStatement(sql);
	                 ResultSet rs = ps.executeQuery();  
	                 List<HtmlSelectOption> list = new ArrayList<HtmlSelectOption>();
	                 while(rs.next()){
	                	 HtmlSelectOption HtmlSelectOption=new HtmlSelectOption();
	                	 HtmlSelectOption.setId(rs.getString("id"));
	                	 HtmlSelectOption.setDisplayName(rs.getString("displayName"));
	                	 list.add(HtmlSelectOption);
	                 }
	                 rs.close();
	                 ps.close();
	                 con.close();
					return list;
				}
			});
			return list;
		}
	/**
	 * 得到维度的下拉框
	 * @param roleId
	 * @return
	 */
	public List<HtmlSelectOption> getProgarmList() {
		
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuffer hql=new StringBuffer();
				String sql="select t.PROGRAM_ID id, t.PROGRAM_NAME displayName from LJW_PROGRAM_T t";
				 Connection con = session.connection();      
                 PreparedStatement ps = con.prepareStatement(sql);
                 ResultSet rs = ps.executeQuery();  
                 List<HtmlSelectOption> list = new ArrayList<HtmlSelectOption>();
                 while(rs.next()){
                	 HtmlSelectOption HtmlSelectOption=new HtmlSelectOption();
                	 HtmlSelectOption.setId(rs.getString("id"));
                	 HtmlSelectOption.setDisplayName(rs.getString("displayName"));
                	 list.add(HtmlSelectOption);
                 }
                 rs.close();
                 ps.close();
                 con.close();
				return list;
			}
		});
		return list;
	}

	/**
	 * 保存
	 * @param user
	 * @return
	 */
	public int saveUserRoleProgram(final LjwUserT user) {
		Integer count = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				  String sql = "INSERT INTO  LJW_USER_ROLE_PROGRAM_T(USER_ID,ROLE_ID,PROGRAM_ID,CREATION_DATE) VALUES (?,?,?,CURDATE())";
				  Connection conn = session.connection();    
				  PreparedStatement ps = conn.prepareStatement(sql);
				  ps.setInt(1, user.getUserId());
				  ps.setInt(2, user.getRoleId());
				  ps.setInt(3, user.getProgramId());
				  int isSuccess=ps.executeUpdate();
				  conn.close(); 
				  ps.close();
				return new Integer(isSuccess);
			}
		});
		return Integer.parseInt(count.toString());
	}
	
	/**
	 * 修改
	 * @param user
	 * @return
	 */
	public int updateUserRoleProgram(final LjwUserT user) {
		Integer count = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				  String sql = "UPDATE LJW_USER_ROLE_PROGRAM_T SET ROLE_ID=?,PROGRAM_ID=?,CREATION_DATE=CURDATE() WHERE USER_ID=?";
				  Connection conn = session.connection();    
				  PreparedStatement ps = conn.prepareStatement(sql);
				  ps.setInt(1, user.getRoleId());
				  ps.setInt(2, user.getProgramId());
				  ps.setInt(3, user.getUserId());
				  int isSuccess=ps.executeUpdate();
				  conn.close(); 
				  ps.close();
				return new Integer(isSuccess);
			}
		});
		return Integer.parseInt(count.toString());
	}
	
	public int deleteUserRoleProgram(final LjwUserT user) {
		Integer count = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				  String sql = "DELETE FROM LJW_USER_ROLE_PROGRAM_T WHERE USER_ID=?";
				  Connection conn = session.connection();    
				  PreparedStatement ps = conn.prepareStatement(sql);
				  ps.setInt(1, user.getUserId());
				  int isSuccess=ps.executeUpdate();
				  conn.close(); 
				  ps.close();
				return new Integer(isSuccess);
			}
		});
		return Integer.parseInt(count.toString());
	}
	
	
	public int checkIsExists(final LjwUserT user) {
		Integer count = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				  String sql = "SELECT COUNT(1) as result FROM LJW_USER_ROLE_PROGRAM_T WHERE USER_ID=? AND ROLE_ID=? AND PROGRAM_ID=?";
				  Connection conn = session.connection();    
				  PreparedStatement ps = conn.prepareStatement(sql);
				  ps.setInt(1, user.getUserId());
				  ps.setInt(2, user.getRoleId());
				  ps.setInt(3, user.getProgramId());
				  ResultSet rs = ps.executeQuery();
				  int result=rs.getInt("result");
				  rs.close();
				  ps.close();
				  conn.close();
				return result;
			}
		});
		return Integer.parseInt(count.toString());
	}
	
	
	public LjwUserT getUserInfoByUserId(final Integer userId) {
		LjwUserT user = (LjwUserT)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				  String sql="SELECT T1.USER_ID userId, T1.USER_NAME userName,T2.ROLE_NAME roleName,T2.ROLE_ID roleId,T3.PROGRAM_ID programId   " +
				  		"FROM LJW_USER_ROLE_PROGRAM_T T INNER JOIN  LJW_USER_T T1 " +
				  		"ON T.USER_ID=T1.USER_ID INNER JOIN LJW_ROLE_T T2 " +
				  		"ON T.ROLE_ID=T2.ROLE_ID INNER JOIN LJW_PROGRAM_T T3 " +
				  		"ON T3.PROGRAM_ID=T.PROGRAM_ID where T1.USER_ID=?";
				  Connection con = session.connection();    
				  PreparedStatement ps = con.prepareStatement(sql);
				  ps.setInt(1, userId);
				  ResultSet rs = ps.executeQuery();
				  LjwUserT user=new LjwUserT();
				  while(rs.next()){
					  user.setUserId(rs.getInt("userId"));
					  user.setUserName(rs.getString("userName"));
					  user.setRoleId(rs.getInt("roleId"));
					  user.setRoleName(rs.getString("roleName")); 
					  user.setProgramId(rs.getInt("programId"));
				  }
				  rs.close();
				  ps.close();
				  con.close();
				return user;
			}
		});
		return user;
	}
	
	
}
